/****** Object:  StoredProcedure [dbo].[AuthZ_spDeleteAllUsersForRole]    Script Date: 06/24/2009 23:39:46 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AuthZ_spDeleteAllUsersForRole]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[AuthZ_spDeleteAllUsersForRole]
GO

/****** Object:  StoredProcedure [dbo].[AuthZ_spDeleteAllUsersForRole]    Script Date: 06/24/2009 23:39:46 ******/
SET ANSI_NULLS ON
GO

-- =============================================
-- Author:		<revans>
-- Create date: <5-13-2009>
-- Description:	<Removes all AuthZUser to Role mappings for a role.>
-- Param:
-- =============================================
CREATE PROCEDURE [dbo].[AuthZ_spDeleteAllUsersForRole]

	@role as nvarchar(50)

AS
BEGIN
	BEGIN TRY
		SET NOCOUNT ON;
		
		--Remove all user mappings from the role.
		DELETE FROM [dbo].[AuthZUserRoleMapping]
		WHERE RoleID IN
		(SELECT RoleID
		 FROM [dbo].[Role]
		 WHERE RoleName = @role)
		
		-- Remove all Users that are no longer mapped to any role.
		DELETE FROM [dbo].[AuthZUser]
		WHERE AuthZUserID NOT IN
		(SELECT au.AuthZUserID
		 FROM [dbo].[AuthZUserRoleMapping] arm
		 JOIN [dbo].[AuthZUser] au
		 ON   au.AuthZUserID = arm.AuthZUserID)

	END TRY
BEGIN CATCH
	DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;

    SELECT @ErrorMessage = ERROR_MESSAGE(),
           @ErrorSeverity = ERROR_SEVERITY(),
           @ErrorState = ERROR_STATE();
           
    RAISERROR (@ErrorMessage, -- Message text.
               @ErrorSeverity, -- Severity.
               @ErrorState -- State.
               );
END CATCH
END

GO


